import os
import sys

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))))

from uuid import uuid4
import pandas as pd
from com.mousechsh.common.middle.conf.MouseChshConfiguration import mousechsh_configuration_set
from com.mousechsh.common.io.db.MouseChshPostgresql import MouseChshPostgresql

current_filename = r'D:\MouseChsh\C\USER\MouseChsh\Downloads\故障修复及时率统计(部门)二级部门详情 (30).xlsx'
current_sheetname = r'sheet1'

mousechsh_configuration_set('database.connection.host', '127.0.0.1')
mousechsh_configuration_set('database.connection.port', '5432')
mousechsh_configuration_set('database.connection.database', 'DB_MouseChshDB')
mousechsh_configuration_set('database.connection.user', 'MouseChsh')
mousechsh_configuration_set('database.connection.password', '123')

def read_excel_sheets():
	xls_obj = pd.read_excel( io=current_filename, header=None, sheet_name=None, nrows=10, keep_default_na=False )
	print(xls_obj)
	for sheet_name in xls_obj:
		print(sheet_name)

def read_excel_sheet_data_fubao_search():
	db = MouseChshPostgresql()
	db.connect()

	count=db.get(
		'''
		SELECT
			count(*) AS "Count"
		FROM "A_WorkTickets"."T_WorkTicket" A
			''', {
		}
	)
	count = count['data'][0][0] * 1
	saved_data = {}
	idx = 0
	while(idx < count):
		data = db.get(
			'''
			SELECT
				A."C_Id",
				A."C_TicketId"
			FROM "A_WorkTickets"."T_WorkTicket" A
			LIMIT 1000 OFFSET %(limit)s
				''', {
				'limit': idx
			}
		)
		idx += 1000
		for item in data['data']:
			saved_data[item[1]] = item[0]

	sheet = pd.read_excel( io=current_filename, header=1, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for line in sheet.loc:
			if not line['故障单号(必填)']:
				continue
			if line['故障单号(必填)'] in saved_data:
				print('更新', '故障单号(必填)', line['故障单号(必填)'], '故障级别', line['故障级别'], '故障主题(必填)', line['故障主题(必填)'][0:30])
				db.execute(
					'''
					UPDATE "A_WorkTickets"."T_WorkTicket" SET
						"X_Data" = %(data)s,
						"C_FB2_TicketCategory" = %(TicketCategory)s,
						"C_FB2_TicketLevel" = %(TicketLevel)s,
						"C_FB2_IsBreakBusiness" = %(IsBreakBusiness)s,
						"C_FB2_TicketCreate" = %(TicketCreate)s,
						"C_FB2_TicketTitle" = %(TicketTitle)s,
						"C_FB2_TicketContent" = %(TicketContent)s,
						"C_FB2_TicketProcess" = %(TicketProcess)s,
						"C_FB2_TicketReason" = %(TicketReason)s,
						"C_FB2_TicketProcessDepartment" = %(TicketProcessDepartment)s,
						"C_FB2_TicketAccountableDepartmentLv1" = %(TicketAccountableDepartmentLv1)s,
						"C_FB2_TicketFixedTime" = %(TicketFixedTime)s,
						"C_FB2_TicketStatus" = %(TicketStatus)s,
						"C_FB2_IsFree" = %(IsFree)s
					WHERE
						"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['业务是否中断'] == '是' else 0 ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketTitle': str( line['故障主题(必填)'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketProcessDepartment': str( line['故障处理人'] ),
						'TicketAccountableDepartmentLv1': str( line['当前主责部门'] ),
						'TicketFixedTime': ( str( line['业务恢复时间'] ) if line['业务恢复时间'] else None ),
						'TicketStatus': str( line['订单状态'] ),
						'IsFree': str( 1 if line['考核减免'] == '是' else 0 ),
						'TicketId': str( line['故障单号(必填)'] )
					}
				)
			else:
				print('添加', '故障单号(必填)', line['故障单号(必填)'], '故障级别', line['故障级别'], '故障主题(必填)', line['故障主题(必填)'][0:30])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
					INSERT INTO "A_WorkTickets"."T_WorkTicket"(
						"C_Id",
						"X_Data",
						"C_TicketId",
						"C_FB2_TicketCategory",
						"C_FB2_TicketLevel",
						"C_FB2_IsBreakBusiness",
						"C_FB2_TicketCreate",
						"C_FB2_TicketTitle",
						"C_FB2_TicketContent",
						"C_FB2_TicketProcess",
						"C_FB2_TicketReason",
						"C_FB2_TicketProcessDepartment",
						"C_FB2_TicketAccountableDepartmentLv1",
						"C_FB2_TicketFixedTime",
						"C_FB2_TicketStatus",
						"C_FB2_IsFree"
					) VALUES (
						%(id)s,
						%(data)s,
						%(TicketId)s,
						%(TicketCategory)s,
						%(TicketLevel)s,
						%(IsBreakBusiness)s,
						%(TicketCreate)s,
						%(TicketTitle)s,
						%(TicketContent)s,
						%(TicketProcess)s,
						%(TicketReason)s,
						%(TicketProcessDepartment)s,
						%(TicketAccountableDepartmentLv1)s,
						%(TicketFixedTime)s,
						%(TicketStatus)s,
						%(IsFree)s
					)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': str( line['故障单号(必填)'] ),
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['业务是否中断'] == '是' else 0 ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketTitle': str( line['故障主题(必填)'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketProcessDepartment': str( line['故障处理人'] ),
						'TicketAccountableDepartmentLv1': str( line['当前主责部门'] ),
						'TicketFixedTime': ( str( line['业务恢复时间'] ) if line['业务恢复时间'] else None ),
						'TicketStatus': str( line['订单状态'] ),
						'IsFree': str( 1 if line['考核减免'] == '是' else 0 )
					}
				)
				saved_data[line['故障单号(必填)']] = new_id
	except Exception as ex:
		print(ex)

	db.commit()
	db.close()



def read_excel_sheet_data_fubao_report():
	db = MouseChshPostgresql()
	db.connect()

	count=db.get(
		'''
		SELECT
			count(*) AS "Count"
		FROM "A_WorkTickets"."T_WorkTicket" A
			''', {
		}
	)
	count = count['data'][0][0] * 1
	saved_data = {}
	idx = 0
	while(idx < count):
		data = db.get(
			'''
			SELECT
				A."C_Id",
				A."C_TicketId"
			FROM "A_WorkTickets"."T_WorkTicket" A
			LIMIT 1000 OFFSET %(limit)s
				''', {
				'limit': idx
			}
		)
		idx += 1000
		for item in data['data']:
			saved_data[item[1]] = item[0]

	sheet = pd.read_excel( io=current_filename, header=1, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for line in sheet.loc:
			if not line['故障单号']:
				continue
			if line['故障单号'] in saved_data:
				print('更新', '故障单号', line['故障单号'], '故障级别', line['故障级别'], '故障主题', line['故障主题'][0:30])
				db.execute(
					'''
					UPDATE "A_WorkTickets"."T_WorkTicket" SET
						"X_Data" = %(data)s,
						"C_FB2_TicketCategory" = %(TicketCategory)s,
						"C_FB2_TicketLevel" = %(TicketLevel)s,
						"C_FB2_IsBreakBusiness" = %(IsBreakBusiness)s,
						"C_FB2_NetworkLevel" = %(NetworkLevel)s,
						"C_FB2_TicketCreate" = %(TicketCreate)s,
						"C_FB2_TicketSend" = %(TicketSend)s,
						"C_FB2_BaseStationId" = %(BaseStationId)s,
						"C_FB2_TicketTitle" = %(TicketTitle)s,
						"C_FB2_TicketContent" = %(TicketContent)s,
						"C_FB2_TicketProcess" = %(TicketProcess)s,
						"C_FB2_TicketReason" = %(TicketReason)s,
						"C_FB2_TicketReasonCategoryLv1" = %(TicketReasonCategoryLv1)s,
						"C_FB2_TicketReasonCategoryLv2" = %(TicketReasonCategoryLv2)s,
						"C_FB2_TicketProcessDepartment" = %(TicketProcessDepartment)s,
						"C_FB2_TicketAccountableDepartmentLv1" = %(TicketAccountableDepartmentLv1)s,
						"C_FB2_TicketAccountableDepartmentLv2" = %(TicketAccountableDepartmentLv2)s,
						"C_FB2_TicketFixedTime" = %(TicketFixedTime)s,
						"C_FB2_TicketWantFixedTime" = %(TicketWantFixedTime)s,
						"C_FB2_DepartProcessLong" = %(DepartProcessLong)s,
						"C_FB2_TicketLong" = %(TicketLong)s,
						"C_FB2_TicketStatus" = %(TicketStatus)s,
						"C_FB2_BreakAreaCount" = %(BreakAreaCount)s,
						"C_FB2_ProcOvertimeTime" = %(ProcOvertimeTime)s,
						"C_FB2_CloseOvertimeTime" = %(CloseOvertimeTime)s,
						"C_FB2_TicketAccountableAudisTime" = %(TicketAccountableAudisTime)s,
						"C_FB2_TicketAudisTime" = %(TicketAudisTime)s,
						"C_FB2_IsProcOvertime" = %(IsProcOvertime)s,
						"C_FB2_IsForceCloseOvertime" = %(IsForceCloseOvertime)s,
						"C_FB2_TicketClass" = %(TicketClass)s,
						"C_FB2_ProcessUser" = %(ProcessUser)s,
						"C_FB2_ReCreateTimes" = %(ReCreateTimes)s,
						"C_FB2_IsGroupTicket" = %(IsGroupTicket)s,
						"C_FB2_GroupTicketId" = %(GroupTicketId)s,
						"C_FB2_CloudClass" = %(CloudClass)s,
						"C_FB2_IsForceClose" = %(IsForceClose)s,
						"C_FB2_IsFree" = %(IsFree)s,
						"C_FB2_IsProjectLeave" = %(IsProjectLeave)s
					WHERE
						"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['是否中断业务'] == '是' else 0 ),
						'NetworkLevel': str( line['网络层级'] ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketSend': ( str( line['故障派发时间'] ) if line['故障派发时间'] else None ),
						'BaseStationId': str( line['基站站号'] ),
						'TicketTitle': str( line['故障主题'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketReasonCategoryLv1': str( line['一级原因分类'] ),
						'TicketReasonCategoryLv2': str( line['二级原因分类'] ),
						'TicketProcessDepartment': str( line['故障当前处理部门'] ),
						'TicketAccountableDepartmentLv1': str( line['故障主责部门'] ),
						'TicketAccountableDepartmentLv2': str( line['故障二级主责部门'] ),
						'TicketFixedTime': ( str( line['故障修复时间'] ) if line['故障修复时间'] else None ),
						'TicketWantFixedTime': ( str( line['故障单要求修复时间'] ) if line['故障单要求修复时间'] else None ),
						'DepartProcessLong': str( line['本部门处理历时'] ),
						'TicketLong': str( line['故障总历时'] ),
						'TicketStatus': str( line['工单状态'] ),
						'BreakAreaCount': str( line['中断小区数'] ),
						'ProcOvertimeTime': ( str( line['故障单要求闭环时间'] ) if line['故障单要求闭环时间'] else None ),
						'CloseOvertimeTime': ( str( line['故障回复结束时间'] ) if line['故障回复结束时间'] else None ),
						'TicketAccountableAudisTime': ( str( line['主责人审核结束时间'] ) if line['主责人审核结束时间'] else None ),
						'TicketAudisTime': ( str( line['故障审核结束时间'] ) if line['故障审核结束时间'] else None ),
						'IsProcOvertime': str( 1 if line['处理超时'] == '是' else 0 ),
						'IsForceCloseOvertime': str( 1 if line['闭环超时'] == '是' else 0 ),
						'TicketClass': str( line['专业细分'] ),
						'ProcessUser': str( line['代维名称'] ),
						'ReCreateTimes': str( line['告警反复次数'] if line['告警反复次数'] else 0 ),
						'IsGroupTicket': str( 1 if line['是否集团单'] == '是' else 0 ),
						'GroupTicketId': str( line['集团编号'] ),
						'CloudClass': str( line['云类别'] ),
						'IsForceClose': str( 1 if line['是否强制闭环'] == '是' else 0 ),
						'IsFree': str( 1 if line['是否减免'] == '是' else 0 ),
						'IsProjectLeave': str( 1 if line['是否工程遗留'] == '是' else 0 ),
						'TicketId': str( line['故障单号'] )
					}
				)
			else:
				print('添加', '故障单号', line['故障单号'], '故障级别', line['故障级别'], '故障主题', line['故障主题'][0:30])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
					INSERT INTO "A_WorkTickets"."T_WorkTicket"(
						"C_Id",
						"X_Data",
						"C_TicketId",
						"C_FB2_TicketCategory",
						"C_FB2_TicketLevel",
						"C_FB2_IsBreakBusiness",
						"C_FB2_NetworkLevel",
						"C_FB2_TicketCreate",
						"C_FB2_TicketSend",
						"C_FB2_BaseStationId",
						"C_FB2_TicketTitle",
						"C_FB2_TicketContent",
						"C_FB2_TicketProcess",
						"C_FB2_TicketReason",
						"C_FB2_TicketReasonCategoryLv1",
						"C_FB2_TicketReasonCategoryLv2",
						"C_FB2_TicketProcessDepartment",
						"C_FB2_TicketAccountableDepartmentLv1",
						"C_FB2_TicketAccountableDepartmentLv2",
						"C_FB2_TicketFixedTime",
						"C_FB2_TicketWantFixedTime",
						"C_FB2_DepartProcessLong",
						"C_FB2_TicketLong",
						"C_FB2_TicketStatus",
						"C_FB2_BreakAreaCount",
						"C_FB2_ProcOvertimeTime",
						"C_FB2_CloseOvertimeTime",
						"C_FB2_TicketAccountableAudisTime",
						"C_FB2_TicketAudisTime",
						"C_FB2_IsProcOvertime",
						"C_FB2_IsForceCloseOvertime",
						"C_FB2_TicketClass",
						"C_FB2_ProcessUser",
						"C_FB2_ReCreateTimes",
						"C_FB2_IsGroupTicket",
						"C_FB2_GroupTicketId",
						"C_FB2_CloudClass",
						"C_FB2_IsForceClose",
						"C_FB2_IsFree",
						"C_FB2_IsProjectLeave"
					) VALUES (
						%(id)s,
						%(data)s,
						%(TicketId)s,
						%(TicketCategory)s,
						%(TicketLevel)s,
						%(IsBreakBusiness)s,
						%(NetworkLevel)s,
						%(TicketCreate)s,
						%(TicketSend)s,
						%(BaseStationId)s,
						%(TicketTitle)s,
						%(TicketContent)s,
						%(TicketProcess)s,
						%(TicketReason)s,
						%(TicketReasonCategoryLv1)s,
						%(TicketReasonCategoryLv2)s,
						%(TicketProcessDepartment)s,
						%(TicketAccountableDepartmentLv1)s,
						%(TicketAccountableDepartmentLv2)s,
						%(TicketFixedTime)s,
						%(TicketWantFixedTime)s,
						%(DepartProcessLong)s,
						%(TicketLong)s,
						%(TicketStatus)s,
						%(BreakAreaCount)s,
						%(ProcOvertimeTime)s,
						%(CloseOvertimeTime)s,
						%(TicketAccountableAudisTime)s,
						%(TicketAudisTime)s,
						%(IsProcOvertime)s,
						%(IsForceCloseOvertime)s,
						%(TicketClass)s,
						%(ProcessUser)s,
						%(ReCreateTimes)s,
						%(IsGroupTicket)s,
						%(GroupTicketId)s,
						%(CloudClass)s,
						%(IsForceClose)s,
						%(IsFree)s,
						%(IsProjectLeave)s
					)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': str( line['故障单号'] ),
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['是否中断业务'] == '是' else 0 ),
						'NetworkLevel': str( line['网络层级'] ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketSend': ( str( line['故障派发时间'] ) if line['故障派发时间'] else None ),
						'BaseStationId': str( line['基站站号'] ),
						'TicketTitle': str( line['故障主题'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketReasonCategoryLv1': str( line['一级原因分类'] ),
						'TicketReasonCategoryLv2': str( line['二级原因分类'] ),
						'TicketProcessDepartment': str( line['故障当前处理部门'] ),
						'TicketAccountableDepartmentLv1': str( line['故障主责部门'] ),
						'TicketAccountableDepartmentLv2': str( line['故障二级主责部门'] ),
						'TicketFixedTime': ( str( line['故障修复时间'] ) if line['故障修复时间'] else None ),
						'TicketWantFixedTime': ( str( line['故障单要求修复时间'] ) if line['故障单要求修复时间'] else None ),
						'DepartProcessLong': str( line['本部门处理历时'] ),
						'TicketLong': str( line['故障总历时'] ),
						'TicketStatus': str( line['工单状态'] ),
						'BreakAreaCount': str( line['中断小区数'] ),
						'ProcOvertimeTime': ( str( line['故障单要求闭环时间'] ) if line['故障单要求闭环时间'] else None ),
						'CloseOvertimeTime': ( str( line['故障回复结束时间'] ) if line['故障回复结束时间'] else None ),
						'TicketAccountableAudisTime': ( str( line['主责人审核结束时间'] ) if line['主责人审核结束时间'] else None ),
						'TicketAudisTime': ( str( line['故障审核结束时间'] ) if line['故障审核结束时间'] else None ),
						'IsProcOvertime': str( 1 if line['处理超时'] == '是' else 0 ),
						'IsForceCloseOvertime': str( 1 if line['闭环超时'] == '是' else 0 ),
						'TicketClass': str( line['专业细分'] ),
						'ProcessUser': str( line['代维名称'] ),
						'ReCreateTimes': str( line['告警反复次数'] if line['告警反复次数'] else 0 ),
						'IsGroupTicket': str( 1 if line['是否集团单'] == '是' else 0 ),
						'GroupTicketId': str( line['集团编号'] ),
						'CloudClass': str( line['云类别'] ),
						'IsForceClose': str( 1 if line['是否强制闭环'] == '是' else 0 ),
						'IsFree': str( 1 if line['是否减免'] == '是' else 0 ),
						'IsProjectLeave': str( 1 if line['是否工程遗留'] == '是' else 0 )
					}
				)
				saved_data[line['故障单号']] = new_id
	except Exception as ex:
		print(ex)

	db.commit()
	db.close()

def read_excel_sheet_data_keda_search():
	db = MouseChshPostgresql()
	db.connect()

	count=db.get(
		'''
		SELECT
			count(*) AS "Count"
		FROM "A_WorkTickets"."T_WorkTicket" A
			''', {
		}
	)
	count = count['data'][0][0] * 1
	saved_data = {}
	idx = 0
	while(idx < count):
		data = db.get(
			'''
			SELECT
				A."C_Id",
				A."C_TicketId"
			FROM "A_WorkTickets"."T_WorkTicket" A
			LIMIT 1000 OFFSET %(limit)s
				''', {
				'limit': idx
			}
		)
		idx += 1000
		for item in data['data']:
			saved_data[item[1]] = item[0]

	sheet = pd.read_excel( io=current_filename, header=0, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for line in sheet.loc:
			if not line['业务号码']:
				continue
			if line['业务号码'] in saved_data:
				print('更新', '业务号码', line['业务号码'], '区域', line['区域'], '外线处理人', line['外线处理人'])
				db.execute(
					'''
					UPDATE "A_WorkTickets"."T_WorkTicket" SET
						"X_Data" = %(data)s,
						"C_KD_Area" = %(Area)s,
						"C_KD_ProcessUser" = %(ProcessUser)s
					WHERE
						"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'Area': line['区域'],
						'ProcessUser': line['外线处理人'],
						'TicketId': line['业务号码']
					}
				)
			else:
				print('添加', '业务号码', line['业务号码'], '区域', line['区域'], '外线处理人', line['外线处理人'])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
					INSERT INTO "A_WorkTickets"."T_WorkTicket"(
						"C_Id",
						"X_Data",
						"C_TicketId",
						"C_KD_Area",
						"C_KD_ProcessUser"
					) VALUES (
						%(id)s,
						%(data)s,
						%(TicketId)s,
						%(Area)s,
						%(ProcessUser)s
					)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': line['业务号码'],
						'Area': line['区域'],
						'ProcessUser': line['外线处理人']
					}
				)
				saved_data[line['业务号码']] = new_id
	except Exception as ex:
		print(ex)

	db.commit()
	db.close()

def read_excel_sheet_data_keda_report():
	db = MouseChshPostgresql()
	db.connect()

	count=db.get(
		'''
		SELECT
			count(*) AS "Count"
		FROM "A_WorkTickets"."T_WorkTicket" A
			''', {
		}
	)
	count = count['data'][0][0] * 1
	saved_data = {}
	idx = 0
	while(idx < count):
		data = db.get(
			'''
			SELECT
				A."C_Id",
				A."C_TicketId"
			FROM "A_WorkTickets"."T_WorkTicket" A
			LIMIT 1000 OFFSET %(limit)s
				''', {
				'limit': idx
			}
		)
		idx += 1000
		for item in data['data']:
			saved_data[item[1]] = item[0]

	sheet = pd.read_excel( io=current_filename, header=0, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for line in sheet.loc:
			if not line['业务号码']:
				continue
			if line['业务号码'] in saved_data:
				print('更新', '业务号码', line['业务号码'], '区域', line['区域'], '处理人', line['处理人'])
				db.execute(
					'''
					UPDATE "A_WorkTickets"."T_WorkTicket" SET
						"X_Data" = %(data)s,
						"C_KD_FixedStatus" = %(FixedStatus)s,
						"C_KD_Area" = %(Area)s,
						"C_KD_ProcessUser" = %(ProcessUser)s
					WHERE
						"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'FixedStatus': line['网管修复标识'],
						'Area': line['区域'],
						'ProcessUser': line['处理人'],
						'TicketId': line['业务号码']
					}
				)
			else:
				print('添加', '业务号码', line['业务号码'], '区域', line['区域'], '处理人', line['处理人'])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
					INSERT INTO "A_WorkTickets"."T_WorkTicket"(
						"C_Id",
						"X_Data",
						"C_TicketId",
						"C_KD_FixedStatus",
						"C_KD_Area",
						"C_KD_ProcessUser"
					) VALUES (
						%(id)s,
						%(data)s,
						%(TicketId)s,
						%(FixedStatus)s,
						%(Area)s,
						%(ProcessUser)s
					)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': line['业务号码'],
						'FixedStatus': line['网管修复标识'],
						'Area': line['区域'],
						'ProcessUser': line['处理人']
					}
				)
				saved_data[line['业务号码']] = new_id
	except Exception as ex:
		print(ex)

	db.commit()
	db.close()

def read_excel_sheet_data_worker_zone():
	db = MouseChshPostgresql()
	db.connect()

	count=db.get(
		'''
		SELECT
			count(*) AS "Count"
		FROM "A_WorkTickets"."T_WorkerGrid" A
			''', {
		}
	)
	count = count['data'][0][0] * 1
	saved_data = {}
	idx = 0
	while(idx < count):
		data = db.get(
			'''
			SELECT
				A."C_Id",
				A."C_Name"
			FROM "A_WorkTickets"."T_WorkerGrid" A
			LIMIT 1000 OFFSET %(limit)s
				''', {
				'limit': idx
			}
		)
		idx += 1000
		for item in data['data']:
			saved_data[item[1]] = item[0]

	sheet = pd.read_excel( io=current_filename, header=0, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for line in sheet.loc:
			if not line['人员']:
				continue
			if line['人员'] in saved_data:
				print('更新', '人员', line['人员'], '区域', line['区域'], '代维', line['代维'])
				db.execute(
					'''
					UPDATE "A_WorkTickets"."T_WorkerGrid" SET
						"X_Data" = %(data)s,
						"C_Area" = %(Area)s,
						"C_Zone" = %(Zone)s
					WHERE
						"C_Name" = %(Name)s
						''', {
						'data': '{}',
						'Area': line['区域'],
						'Zone': line['代维'],
						'Name': line['人员']
					}
				)
			else:
				print('添加', '人员', line['人员'], '区域', line['区域'], '代维', line['代维'])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
					INSERT INTO "A_WorkTickets"."T_WorkerGrid"(
						"C_Id",
						"X_Data",
						"C_Name",
						"C_Area",
						"C_Zone"
					) VALUES (
						%(id)s,
						%(data)s,
						%(Name)s,
						%(Area)s,
						%(Zone)s
					)
						''', {
						'id': new_id,
						'data': '{}',
						'Name': line['人员'],
						'Area': line['区域'],
						'Zone': line['代维']
					}
				)
				saved_data[line['人员']] = new_id
	except Exception as ex:
		print(ex)

	db.commit()
	db.close()

#############################

# read_excel_sheets()

# read_excel_sheet_data_fubao_search()

read_excel_sheet_data_fubao_report()

# read_excel_sheet_data_keda_search()

# read_excel_sheet_data_keda_report()

# read_excel_sheet_data_worker_zone()
